package edu.utdallas_elearn_calendar.utdallas_elearn_calendar_db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class CalDatabaseHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "UniversityDataStorage";
	private static final String DB_TABLE_UNIV_CLASS = "univ_classes";
	private static final String DB_TABLE_UNIV_CLASS_DETAIL = "univ_class_detail";
	private static final String DB_TABLE_EVENTS = "events";
	private static final String DB_TABLE_EVENT_REMIND = "event_remind";
	private static final String DB_TABLE_EVENT_TYPES = "event_types";
	private static final String DB_TABLE_PRIORITY_LEVEL = "priority_level";
	private static final String DB_TABLE_ELEARN_LOG = "elearn_log";
	private static final String DB_VIEW_UNIV_CLASS = "v_univ_classes";  // combines the class with details
	private static final String DB_VIEW_EVENTS = "v_events";			// combines the events with the event types, reminders, and priorities

	private static final int DATABASE_VERSION = 1;

	// Database creation sql statement
	private static final String CREATE_CLASS_TABLE = "create table " +
			DB_TABLE_UNIV_CLASS + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"prefix STRING, " +
			"class_number STRING, " +
			"class_section STRING, " +
			"title STRING)";	

	private static final String CREATE_CLASS_DETAIL_TABLE = "create table " + 
			DB_TABLE_UNIV_CLASS_DETAIL + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"class_id INTEGER, " +
			"semester STRING, " + 
			"professor STRING, " + 
			"time STRING," + 
			"location STRING)";
	
	private static final String CREATE_EVENT_TABLE = "create table " + 
			DB_TABLE_EVENTS + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"class_id INTEGER, " +
			"event_type_id INTEGER, " + 
			"name STRING, " + 
			"due_date STRING," + 
			"recurring_type INTEGER)";
	
	private static final String CREATE_EVENT_REMIND_TABLE = "create table " + 
			DB_TABLE_EVENT_REMIND + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"event_id INTEGER, " +
			"minutes_prior INTEGER)";
	
	private static final String CREATE_EVENT_TYPES_TABLE = "create table " + 
			DB_TABLE_EVENT_TYPES + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"priority_id INTEGER, " +
			"event_type STRING)";	
	
	private static final String CREATE_PRIORITY_LEVEL_TABLE = "create table " + 
			DB_TABLE_PRIORITY_LEVEL + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"priority_val INTEGER, " +
			"color STRING)";	
	
	private static final String CREATE_ELEARN_LOG_TABLE = "create table " + 
			DB_TABLE_ELEARN_LOG + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"log_date STRING, " +
			"log_status STRING)";
	
	private static final String CREATE_CLASS_VIEW = "create view " + 
			DB_VIEW_UNIV_CLASS + " AS " +
			"SELECT " + DB_TABLE_UNIV_CLASS + "._id AS class_id, " + 
			DB_TABLE_UNIV_CLASS + ".prefix AS prefix, " +
			DB_TABLE_UNIV_CLASS + ".class_number AS class_number, " +
			DB_TABLE_UNIV_CLASS + ".class_section AS class_section, " +
			DB_TABLE_UNIV_CLASS + ".title AS title, " +
			DB_TABLE_UNIV_CLASS_DETAIL + "._id AS detail_id, " +
			DB_TABLE_UNIV_CLASS_DETAIL + ".semester AS semester, " +
			DB_TABLE_UNIV_CLASS_DETAIL + ".professor AS professor, " +
			DB_TABLE_UNIV_CLASS_DETAIL + ".time AS time, " +
			DB_TABLE_UNIV_CLASS_DETAIL + ".location AS location " +
			"FROM " + DB_TABLE_UNIV_CLASS + " LEFT OUTER JOIN " +
			DB_TABLE_UNIV_CLASS_DETAIL + " ON (" + 
			DB_TABLE_UNIV_CLASS + "._id = " + DB_TABLE_UNIV_CLASS_DETAIL + ".class_id)";
	
	private static final String CREATE_EVENT_VIEW = "create view " + 
			DB_VIEW_EVENTS + " AS " +
			"SELECT " + DB_TABLE_EVENTS + "._id AS event_id, " + 
			DB_TABLE_EVENTS + ".class_id AS class_id, " +
			DB_TABLE_EVENTS + ".name AS name, " +
			DB_TABLE_EVENTS + ".due_date AS due_date, " +
			DB_TABLE_EVENTS + ".recurring_type AS recurring_type, " +
			DB_TABLE_EVENTS + ".event_type_id AS event_type_id, " +
			DB_TABLE_EVENT_TYPES + ".event_type AS event_type, " +
			DB_TABLE_EVENT_TYPES + ".priority_id AS priority_id, " +
			DB_TABLE_PRIORITY_LEVEL + ".priority_val AS priority_val, " +
			DB_TABLE_PRIORITY_LEVEL + ".color AS color, " +
			DB_TABLE_EVENT_REMIND + "._id AS reminder_id, " +
			DB_TABLE_EVENT_REMIND + ".minutes_prior AS minutes_prior "+
			"FROM " + DB_TABLE_EVENTS + " INNER JOIN " +
			DB_TABLE_EVENT_TYPES + " ON (" + 
			DB_TABLE_EVENTS + ".event_type_id = " + DB_TABLE_EVENT_TYPES + "._id) " +
			"INNER JOIN " + DB_TABLE_PRIORITY_LEVEL + " ON (" +
			DB_TABLE_EVENT_TYPES + ".priority_id = " + DB_TABLE_PRIORITY_LEVEL + "._id) " +
			"LEFT OUTER JOIN " + DB_TABLE_EVENT_REMIND + " ON (" +
			DB_TABLE_EVENTS + "._id = " + DB_TABLE_EVENT_REMIND + ".event_id)";	

	
	
	public CalDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Method is called during creation of the database
	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(CREATE_CLASS_TABLE);
		database.execSQL(CREATE_CLASS_DETAIL_TABLE);
		database.execSQL(CREATE_EVENT_TABLE);
		database.execSQL(CREATE_EVENT_REMIND_TABLE);
		database.execSQL(CREATE_EVENT_TYPES_TABLE);
		database.execSQL(CREATE_PRIORITY_LEVEL_TABLE);
		database.execSQL(CREATE_CLASS_VIEW);
		database.execSQL(CREATE_EVENT_VIEW);
		database.execSQL(CREATE_ELEARN_LOG_TABLE);
	}

	// Method is called during an upgrade of the database, e.g. if you increase
	// the database version
	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		Log.w(CalDatabaseHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		database.execSQL("DROP VIEW IF EXISTS " + DB_VIEW_UNIV_CLASS);
		database.execSQL("DROP VIEW IF EXISTS " + DB_VIEW_EVENTS);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_UNIV_CLASS);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_UNIV_CLASS_DETAIL);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_EVENTS);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_EVENT_REMIND);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_EVENT_TYPES);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_PRIORITY_LEVEL);
		database.execSQL("DROP TABLE IF EXISTS " + DB_TABLE_ELEARN_LOG);
		onCreate(database);
	}

}